package Classes;

import Forms.ServerFrame;
import java.lang.String;
import java.sql.*;
import javax.swing.JOptionPane;
import java.util.*;


/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor...
 */

/*
 *
 * 
 * @author mau
 */
public class Database {
    static ServerFrame sf = null;
    String url = "jdbc:mysql://localhost:3306/hr";
    String driver = "com.mysql.jdbc.Driver";
    String un = "root";
    String pw = "";
    ArrayList<String> usernames = null;
    ArrayList<String> classnames = null;
    int rowCount = 0;
 
    
    static Connection c = null;
    static PreparedStatement ps = null;
    static ResultSet rs = null;
    
    static ArrayList<User> users = null;
    static ArrayList<User> usersList = null;
    static ArrayList<User> userEdit = null;

    public Database(ServerFrame a) {
        try {
            Class.forName(driver).newInstance();
            c = (Connection) DriverManager.getConnection(url, un, pw);
            sf = a;
            sf.appendTextArea("Database Connection Successful. Server is ready.");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Please start xampp server", "Error", JOptionPane.ERROR_MESSAGE);
            System.exit(0);
        }
    }
    
    
     
    public User getUser(String username) {
        User user = null;
        try{
            ps = c.prepareStatement("Select userID, userLName, userGName, userMName, userName, password, userStatus, userType From user LEFT JOIN usert ON user.usertID = usert.usertID where userName = '" + username + "'");
            rs = ps.executeQuery();

            if (rs != null) {
                while(rs.next()) {
                    user = new User(rs.getString(1), rs.getString(2) , rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
                    user.toString();
                }
            }
        } catch(Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }         
        return user;
    } 
    
    public void disconnect() {
        try {
            c.close();
            ServerFrame.serverArea.append("\nDatabase server has stopped running.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void changeUserStatus(String username, String status) throws Exception {
        ps = c.prepareStatement("Update User Set userStatus = '" + status + "' where username = '" + username + "'");
        int r = ps.executeUpdate();
        
    }
    
    public boolean checkStatus(String username) throws Exception {
        ps = c.prepareStatement("select userStatus from user where username = '" + username + "'");
        rs = ps.executeQuery();
        rs.next();
        boolean in = false;
        if(rs.getString(1).equals("In")) {
            in = true;
        }
        return in;
    }    
    
    
    
    public ArrayList<Object> getList(String table) {
        ArrayList<Object> result = null;
                
  
             
        if(table.equals("getlist-users")) {
            try {
                ps = c.prepareStatement("Select userID, userLName, userGName, userMName, userName, password, userStatus, userType From user LEFT JOIN usert ON user.usertID = usert.usertID WHERE user.status = 'active'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();

                if (rs != null) {
                    while(rs.next()) {
                        User u = new User(rs.getString(1), rs.getString(2) , rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
                        result.add(u);
                    }
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }      
        
       
        
        if (table.equals("getlist-accounts")) {
            try {
                ps = c.prepareStatement("SELECT userID, userName, userType FROM user JOIN usert ON user.usertID = usert.usertID where user.status = 'active'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        User us = new User(rs.getString(1), rs.getString(2), rs.getString(3));
                        result.add(us);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        

        
     
        if (table.equals("getlist-acctType")) {
            try {
                ps = c.prepareStatement("SELECT userType FROM usert");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        String ut = rs.getString(1);
                        result.add(ut);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (table.equals("getlist-section")) {
            try {
                ps = c.prepareStatement("SELECT secName, slots FROM section");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        String sec = rs.getString(1);
                        result.add(sec);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        // end
  
        
        if (table.equals("getlist-sumsy")) {
            try {
                ps = c.prepareStatement("select syName from sprogsy;");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if (rs != null) {
                    while (rs.next()) {
                        String s = rs.getString(1);
                        result.add(s);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }   
        return result;
    }
    

    public ArrayList<Object> getList6(String table, String a, String b, int cc){
        ArrayList<Object> result = null;
        if(table.equals("grades-report")){
            try{
                //ps = c.prepareStatement("SELECT student.stuID, IDNo, stuGName, stuMName, stuLName, 1st, 2nd, 3rd, 4th, grade.graID, subject.subName FROM grade JOIN student ON grade.stuID = student.stuID JOIN subject ON grade.subID = subject.subID WHERE student.syID =  '"+a+"' AND student.classID =  '"+b+"' AND student.stuID =  '"+c+"'");
                rs = ps.executeQuery();
                result = new ArrayList<Object>();
                if(rs != null){
                    while(rs.next()){
                        ////Student s = new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString)
                        //result.add(s);
                    }
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        
    
        
        return result;
    }

    
//     public boolean add(String table, Object a, String x, User aa) {
//        boolean success = false;        
//        
//        if (table.equals("add-monthsSY")) {
//            try { 
//                Month m = (Month) a;
//                Statement st = c.createStatement();
//                String query = "INSERT INTO months (months, noOfSchoolDays, syID) VALUES ('"+m.getmonths()+"', '"+m.getNumDays()+"', '"+m.getSy()+"')";
//                st.executeUpdate(query);
//                System.out.println(query);
//                success = true;
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//        }
//        
//        if (table.equals("add-secclassadvsy")) {
//            try {
//                Sections sec = (Sections) a;
//                Statement st = c.createStatement();
//                String query = "INSERT INTO currsecaddvID (currlevelsyID, secID, addcatID) VALUES ('')";
//                st.executeUpdate(query);
//                success = true;
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//        }
//        
//        if (table.equals("add-classSecAdv")) {
//            try {
//                Sections ad = (Sections) a;
//                Statement st = c.createStatement();
//                String query = "INSERT INTO currsecaddv (currlevelsyID, secID, addcatID) VALUES ('"+ad.getcurrlevelsyId()+"', '"+ad.getSecID()+"', '"+ad.getaddcatID()+"')";
//                System.out.println(query);
//                st.executeUpdate(query);
//                success = true;
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//        }
//
//        if (table.equals("add-studgrade")) {
//            try {
////                Grades1 g = (Grades1) a;
////                Statement st = c.createStatement();                
////                String query = "INSERT INTO grade (syID, subID, stuID, 1st, 2nd, 3rd, 4th) VALUES ('" + g.getSyID() + "', '" + g.getsubID() + "', '" + g.getstuID() + "', '" + g.getfirst() + "', '" + g.getsecond() + "', '" + g.getthird() + "', '" + g.getfourth() + "')";
////                System.out.println(query);
////                st.executeUpdate(query);
////                success = true;
//                Grades1 g = (Grades1) a;
//        System.out.println("x " + x);
//        System.out.println("sy " + g.getSyID());
//        System.out.println("sub " + g.getsubID());
//        System.out.println("stu " + g.getstuID());
//        System.out.println("1 " + g.getfirst());
//        System.out.println("2 " + g.getsecond());
//        System.out.println("3 " + g.getthird());
//        System.out.println("4 " + g.getfourth());
//        Statement st = c.createStatement();
//        String query = "";
//        if(x.equals("1")) {
//            query = "INSERT INTO grade (syID, subID, stuID, 1st) VALUES ('" + g.getSyID() + "', '" + g.getsubID() + "', '" + g.getstuID() + "', '" + g.getfirst() + "')";
//        } else {
//            if(x.equals("2")) {
//                query = "UPDATE grade SET 2nd = '" + g.getsecond()+"' where stuID = '" + g.getstuID() + "' and subID = '"+g.getsubID()+"'";
//            } else if(x.equals("3")) {
//                query = "UPDATE grade SET 3rd = '" + g.getthird()+"' where stuID = '" + g.getstuID() + "' and subID = '"+g.getsubID()+"'";
//            } else {
//                query = "UPDATE grade SET 4th = '" + g.getfourth()+"' where stuID = '" + g.getstuID() + "' and subID = '"+g.getsubID()+"'";
//            }
//        }
//        st.executeUpdate(query);
//        success = true;
//            } catch (Exception e) {                
//                e.printStackTrace();
//            }
//        }        
//
//        if (table.equals("add-currsubsy")) {
//            try {
//                Subject2 sub = (Subject2) a;
//                Statement st = c.createStatement();                
//                String query = "INSERT INTO currsubsy (currsubID, syID) VALUES ('" + sub.getCurrsubID() + "', '" + sub.getSyID() + "')";
//                st.executeUpdate(query);                
//                success = true;
//            } catch (Exception e) {                
//                e.printStackTrace();
//            }
//        }
//        
//        if(table.equals("add-currclevelsy")) {
//            try {
//                ClassLevel cl = (ClassLevel) a;
//                Statement st = c.createStatement();
//                String query = "INSERT INTO currlevelsy(currclevelID, syID) VALUES ('"+cl.getCurrclevel_id()+"', '"+cl.getSy_id()+"')";
//                st.executeUpdate(query);
//                success = true;                
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//        }
//        
//        if(table.equals("add-curriculum")) {
//            try {
//                Curriculum cu = (Curriculum) a;
//                Statement st = c.createStatement();
//                String newCurr = "INSERT INTO curriculum(currName, dateEncoded, curriculum.desc, dateEffective) VALUES ('" + cu.getCurrName() + "', '" + cu.getCurrDate() + "', '" + cu.getCurrDesc() + "', '" + cu.getCurrEffective() + "')";
//                st.executeUpdate(newCurr);
//                success = true;
//                sf.appendTextArea(aa.getUsername() + " : Added a new Curriculum - " + cu.getCurrName());
//            }catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }   
//        }
//        
//        if(table.equals("add-sy")) {
//            try {
//                SchoolYear sy = (SchoolYear) a;
//                Statement s = c.createStatement();
//                String query = "INSERT INTO schoolyear (name, startOfClass, endOfClass, numOfDays) VALUES ('" + sy.getSyName() + "', '" + sy.getSyStart() + "', '" + sy.getSyEnd() + "', '" + sy.getNumOfDays() + "')";
//                s.executeUpdate(query);
//                success = true;
//                sf.appendTextArea(aa.getUsername() + " : Added a new School Year - " + sy.getSyName());
//            }catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }
//        }
//        
//        if(table.equals("add-currsy")) {
//            try {
//                SchoolYear sy = (SchoolYear) a;
//                Statement s = c.createStatement();
//                String query = "INSERT INTO sycurr (currID, syID) VALUES ('" + sy.getCurrID() + "', '" + sy.getSyID() + "')";
//                s.executeUpdate(query);
//                success = true;
//            }catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }
//        }
//        
//        if(table.equals("add-currclevel")) {
//            try {
//                ClassLevel cc = (ClassLevel) a;
//                Statement s = c.createStatement();
//                String query = "INSERT INTO currclevel (currID, classID) VALUES ('" + cc.getCurrID() + "', '" + cc.getClassID() + "')";
//                s.executeUpdate(query);
//                success = true;
//            }catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }
//        }
//        
//        if(table.equals("add-currsub")) {
//            try {
//                Subject cc = (Subject) a;
//                Statement s = c.createStatement();
//                String query = "INSERT INTO currsub (currID, currclevelID, subID) VALUES ('" + cc.getCurrID() + "', '" + cc.getCurrclevelID() + "', '" + cc.getsubID() + "')";
//                s.executeUpdate(query);
//                success = true;
//            }catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }
//        }
//        
//        if(table.equals("add-currclsec")) {
//            try {
//                Sections cc = (Sections) a;
//                Statement s = c.createStatement();
//                String query = "INSERT INTO currclsec (secID, currclID, userID) VALUES ('" + cc.getSecID() + "', '" + cc.getCurrclID() + "', '" + cc.getAdv().getId() + "')";
//                s.executeUpdate(query);
//                success = true;
//            }catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }
//        }        
//        
//        if(table.equals("add-classlevel")) {
//            try {
//                ClassLevel cc = (ClassLevel) a;
//                Statement s = c.createStatement();
//                String query = "INSERT INTO classlevel (className, clcatID) VALUES ('" + cc.getClassName() + "', '" + cc.getCatID() + "')";
//                s.executeUpdate(query);
//                success = true;
//            }catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }
//        } 
//        
//        if(table.equals("add-clevels")){
//            try{
//                ClassLevel cl = (ClassLevel) a;
//                ps = c.prepareStatement("Select clcatID from clevelcat where clevelcat.categoryName = '" + cl.getClassCategory() + "'");
//                rs = ps.executeQuery();
//                
//                int cc = 0;
//                if(rs != null){
//                    while(rs.next()){
//                        cc = rs.getInt(1);
//                    }
//                }
//                Statement s = c.createStatement();
//                String insert = "INSERT INTO classlevel (className, clcatID) VALUES ('" + cl.getClassName() + "', '" + cc + "')";
//                s.executeUpdate(insert);
//                success = true;
//                sf.appendTextArea(aa.getUsername() + " : Added a new class level - " + cl.getClassName());
//            }catch(Exception e){
//                success = false;
//                e.printStackTrace();
//            }
//        }
//        
//        if(table.equals("add-sections")){
//            try{                               
//                Sections se = (Sections) a;
//                
//                ps = c.prepareStatement("SELECT classlevel.classID FROM classlevel WHERE classlevel.className = '" + se.getClasslevel() + "'");
//                rs = ps.executeQuery();
//                int cl = 0;
//                if (rs != null) {
//                    while (rs.next()) {
//                        cl = rs.getInt(1);
//                    }
//                }
//                
//                Statement s = c.createStatement();
//                String insert = "INSERT INTO section (secName, slots, classID) VALUES ('" + se.getSect() + "', '" + se.getSlot() +"', '" + cl + "')";
//                s.executeUpdate(insert);
//                success = true;
//                sf.appendTextArea(aa.getUsername() + " : Added a new section - " + se.getSect());
//            }catch(Exception e){
//                success = false;
//                e.printStackTrace();
//            }
//        }
//        
//        if(table.equals("add-subject")) {
//            try {             
//                Subject ss = (Subject) a;
//                ps = c.prepareStatement("Select classID from classlevel where classlevel.className = '" + ss.getClassName() + "'");
//                rs = ps.executeQuery();
//                
//                int cl = 0;
//                if(rs != null){
//                    while(rs.next()){
//                        cl = rs.getInt(1);
//                    }
//                }
//                
//                Statement s = c.createStatement();
//                String insert = "INSERT INTO subject (subName, subject.desc, unit, subType, classID) VALUES('" + ss.getSubjName() + "', '" + ss.getDesc() + "', '" + ss.getUnits() + "', '" + ss.getSubType() + "', '" + cl + "')";
//                s.executeUpdate(insert);
//                success = true;
//                sf.appendTextArea(aa.getUsername() + " : Added a new subject - " + ss.getSubjName());
//                //sf.appendTextArea(aa.getUsername() + " : Added a new Curriculum - " + cl.getSubjName());
//                
//            }catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }
//        }        
//        
//        
//        if(table.equals("add-section")) {
//            try {
//                Sections ss = (Sections) a;
//                Statement s = c.createStatement();
//                String query = "INSERT INTO section (secName, classID) VALUES ('" + ss.getSect() + "', '" + ss.getClassID() + "')";
//                s.executeUpdate(query);
//                success = true;
//                sf.appendTextArea(aa.getUsername() + " : Added a new section - " + ss.getSect());
//            }catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }
//        }
//        // APRIL 28
//        if(table.equals("add-student")) {
//            try {
//                
//                Student st = (Student) a;
//                
//                ps = c.prepareStatement("SELECT classID FROM classlevel WHERE classlevel.className = '" + st.getClassLevel() + "'");
//                rs = ps.executeQuery();
//                
//                int classlevel = 0;              
//                if (rs!=null){
//                    while(rs.next()){
//                        classlevel = rs.getInt(1);
//                    }
//                }
//                
//                ps = c.prepareStatement("SELECT currID FROM curriculum WHERE curriculum.currName = '" + st.getCurrID() + "'");
//                rs = ps.executeQuery();
//                int curriculum = 0;
////                ps = c.prepareStatement("Select currID From curriculum Where curriculum.currName = '" + stu.getCurrID() + "'");
//
//                if(rs != null) {
//                    while(rs.next()) {
//                        curriculum = rs.getInt(1);
//                    }
//                }
//                
//                ps = c.prepareStatement("SELECT syID FROM schoolyear WHERE schoolyear.name = '" + st.getSchoolYear() + "'");
//                rs = ps.executeQuery();
//                int syname = 0;
//                
//                if(rs != null) {
//                    while(rs.next()) {
//                        syname = rs.getInt(1);
//                    }
//                }
//                
//                Statement s = c.createStatement();
//                String query = "INSERT INTO student(IDNo, stuLName, stuGName, stuMName, cAddress, bDate, birthPlace, gender, cAffiliation, cNo, fName, fOccu, fCNo, mName, mOccu, mCNo, lSchoolAttended, lSAdd, oldGenAverage, classID, currID, syID) VALUES ('" + st.getSID() + "','" + st.getLName() + "','" + st.getGName() + "','" + st.getMNane() + "','" + st.getCAddress() + "','" + st.getBirthday() + "','" + st.getBirthPlace() + "','" + st.getGender() + "','" + st.getChurchAffiliate() + "','" + st.getContact() + "','" + st.getFatherName() + "','" + st.getFatherOccupation() + "','" + st.getFatherContact() + "','" + st.getMotherName() + "','" + st.getMotherOccupation() + "','" + st.getMotherContact() + "','" + st.getLastSchoolAttended() + "','" + st.getLastSchoolAddres() + "','" + st.getGeneralAverage() + "', '" + classlevel + "', '" + curriculum + "', '" + syname + "')";
//                s.executeUpdate(query);
//                success = true;
//            } catch (Exception e) {
//                success = false;
//                e.printStackTrace();
//            }
//        }
        // end
       
    }
    

